# !/usr/bin/env python
# -*- coding: utf-8 -*-
# @File  : sqlite3库简单使用.py
# @Author: dongguangwen
# @Date  : 2024-06-22 13:14

import sqlite3

# 连接到数据库（如果不存在，则创建）
conn = sqlite3.connect('example.db')

# 创建一个 Cursor 对象
cursor = conn.cursor()

# 创建表
cursor.execute('''CREATE TABLE IF NOT EXISTS users
               (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')

# 插入数据
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")

# 提交事务
conn.commit()

# 查询数据
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

# 更新数据
cursor.execute("UPDATE users SET age = 31 WHERE name = 'Alice'")

# 删除数据
cursor.execute("DELETE FROM users WHERE name = 'Bob'")

# 提交事务
conn.commit()

# 查询数据
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

# 关闭 Cursor 和 Connection
cursor.close()
conn.close()
